alter table business 
add IsRegistered bit not null default(1)
go
ALTER PROCEDURE [dbo].[spAPI_Business_CheckToken]
(
	@Token VARCHAR(255),
	@IgnoreActiveState bit = 0, 
	@Result INT OUT,
	@BusinessId INT = NULL OUT
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @ExpirationDate DATETIME
	SET @Result = 0
	
	SELECT @ExpirationDate = UserToken.ExpirationDate, @BusinessId = Business.BusinessId
	FROM UserToken INNER JOIN Business ON UserToken.LoginName = Business.[Login]
	WHERE (UserToken.SecurityToken = @Token)
		AND ((Business.IsActive = 1) or (@IgnoreActiveState = 1))
	
	IF (@ExpirationDate IS NOT NULL)
	BEGIN
		IF (GETUTCDATE() > @ExpirationDate)
		BEGIN
			DELETE 
			FROM UserToken 
			WHERE SecurityToken = @Token
			
			SET @BusinessId = NULL
		END
		ELSE
			SET @Result = 1
	END
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_RegisterStep1Update]
	@Token VARCHAR(255)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(5)
	,@latitude float = null
	,@longitude float = null
	,@gmt int = 0
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON

	DECLARE @BusinessId INT		
	DECLARE @IdResult INT
	SET @IdResult = 1
	
	EXEC spAPI_Business_CheckToken @Token = @Token, @IgnoreActiveState = 1, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	if @IdResult = 0
	begin
		SELECT @IdResult AS IdResult
		return
	end
	
	IF ISNULL(@zip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
		BEGIN
			SET @IdResult = -3
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	declare @oldEmail varchar(100) = (select email from Business (nolock) where BusinessId = @BusinessId)
	IF (ISNULL(@Email, '') <> '') and @email <> @oldEmail
	begin
		IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
			EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
		BEGIN
			SET @IdResult = -2
			SELECT @IdResult AS IdResult
			RETURN
		END
	end

	update [Business]
	set
		[Email] = @Email
		,[Password] = @Password
		,[BusinessName] = @BusinessName
		,[Web] = @Web
		,[BusinessPhone] = @BusinessPhone
		,[CellPhone] = @CellPhone
		,[TollFreePhone] = @TollFreePhone
		,[Fax] = @Fax
		,[StreetAddress1] = @StreetAddress
		,[StreetAddress2] = @StreetAddress2
		,[City] = @City
		,[StateCode] = @State
		,[ZipCode] = @Zip
		,[Latitude] = @Latitude
		,[Longitude] = @Longitude
		,[Gmt] = @gmt
	where BusinessId = @BusinessId
	
	SELECT 1 AS IdResult
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_RegisterStep1]
	@login varchar(50)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(5)
	,@latitude float = null
	,@longitude float = null
	,@gmt int = 0
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	SET @IdResult = 1
	declare @BusinessId int
	
	IF ISNULL(@zip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
		BEGIN
			SET @IdResult = -3
			SELECT @IdResult AS IdResult, NULL AS SecurityToken
			RETURN
		END
	
	IF ISNULL(@Email, '') <> ''
		IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
			EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
		BEGIN
			SET @IdResult = -2
			SELECT @IdResult AS IdResult, NULL AS SecurityToken
			RETURN
		END
	
	BEGIN TRANSACTION
	
		INSERT INTO UserBusinessLogins(LoginName, LoginType)
		VALUES(@login, 'B')
		if @@error <> 0 return
			
		INSERT INTO [Business]
			([Login]
			,[Email]
			,[Password]
			,[BusinessName]
			,[Web]
			,[BusinessPhone]
			,[CellPhone]
			,[TollFreePhone]
			,[Fax]
			,[StreetAddress1]
			,[StreetAddress2]
			,[IsActive]
			,[Deleted]
			,[City]
			,[StateCode]
			,[ZipCode]
			,[Latitude]
			,[Longitude]
			,Gmt
			,IsRegistered)
		 VALUES
			(@Login
			,@Email
			,@Password
			,@BusinessName
			,@Web
			,@BusinessPhone
			,@CellPhone
			,@TollFreePhone
			,@Fax
			,@StreetAddress
			,@StreetAddress2
			,0
			,0
			,@City
			,@State
			,@Zip
			,@Latitude
			,@Longitude
			,@gmt
			,0)		
		
		set @BusinessId = SCOPE_IDENTITY()
		exec spAccount_Add @BusinessId = @BusinessId
		
		declare @SecurityToken uniqueidentifier = NEWID()
		INSERT INTO UserToken(SecurityToken, LoginName, ExpirationDate)
		VALUES(@SecurityToken, @Login, '99991231')
		
		SELECT @IdResult AS IdResult, @SecurityToken AS SecurityToken

	COMMIT TRANSACTION
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_RegisterStep2]
	@Token VARCHAR(255)
	,@description varchar(MAX)
	,@referencedBy varchar(100)
	,@sunFrom datetime
	,@sunTo datetime
	,@sunClosed bit
	,@monFrom datetime
	,@monTo datetime
	,@monClosed bit
	,@tueFrom datetime
	,@tueTo datetime
	,@tueClosed bit
	,@wedFrom datetime
	,@wedTo datetime
	,@wedClosed bit
	,@thuFrom datetime
	,@thuTo datetime
	,@thuClosed bit
	,@friFrom datetime
	,@friTo datetime
	,@friClosed bit
	,@satFrom datetime
	,@satTo datetime
	,@satClosed bit
	,@categories varchar(max)
	,@newCategory1 varchar(250)
	,@newCategory2 varchar(250)
	,@newCategory3 varchar(250)
	,@creditCardNo varchar(20)
	,@creditCardExpDate datetime
	,@creditCardName varchar(50)
	,@creditCardPin varchar(10)
	,@useBusinessAddress bit
	,@billingStreetAddress varchar(100)
	,@billingStreetAddress2 varchar(100)
	,@billingCity varchar(50)
	,@billingState varchar(2)
	,@billingZip varchar(5)
	
	,@PictureContentType VARCHAR(50) = ''
	,@Picture varbinary(max) = null	
	
	,@latitude float = null
	,@longitude float = null
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @IgnoreActiveState = 1, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	if @IdResult = 0
	begin
		SELECT @IdResult AS IdResult
		return
	end
	
	SET @IdResult = 1
	
	IF ISNULL(@referencedBy, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM UserBusinessLogins (nolock) WHERE LoginName = @referencedBy)
		BEGIN
			SET @IdResult = 0
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	IF ISNULL(@billingZip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode (nolock) WHERE ZipCode = @billingZip)
		BEGIN
			SET @IdResult = -4
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	BEGIN TRANSACTION
		
		update [Business]
		set
			[Description] = @Description
			,[CreditCardNo] = @CreditCardNo
			,[CreditCardExpDate] = @CreditCardExpDate
			,[CreditCardName] = @CreditCardName
			,[CreditCardPin] = @CreditCardPin
			,[BillingAddress1] = @BillingStreetAddress
			,[BillingAddress2] = @BillingStreetAddress2
			,[BillingCity] = @BillingCity
			,[BillingStateCode] = @BillingState
			,[BillingZipCode] = @BillingZip
			,[BillingUse] = @useBusinessAddress
			,[ReferencedBy] = @ReferencedBy
			,PictureContentType = @PictureContentType
			,Picture = @Picture
			,latitude = @latitude
			,longitude = @longitude
			,IsActive = 1
			,IsRegistered = 1
		where BusinessId = @businessId	
		
		
		delete from BusinessHours where BusinessId = @BusinessId
		
		INSERT INTO BusinessHours(BusinessId, [WeekDay], FromTime, ToTime, Closed)
		values
			(@BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed)
			,(@BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed)
			,(@BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed)
			,(@BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed)
			,(@BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed)
			,(@BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed)
			,(@BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed)
    
    
		delete from BusinessCategory where BusinessId = @BusinessId
    
		INSERT INTO BusinessCategory(BusinessId, CategoryId)
		SELECT @BusinessId, CONVERT(INT, Items.Item)
		FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
		
		
		IF (@newCategory1 IS NOT NULL) and (@newCategory1 <> '')
			INSERT INTO CategoryRequest
			(BusinessId, CategoryName)
			VALUES (@BusinessId, @newCategory1)
		IF (@newCategory2 IS NOT NULL) and (@newCategory2 <> '')
			INSERT INTO CategoryRequest
			(BusinessId, CategoryName)
			VALUES (@BusinessId, @newCategory2)
		IF (@newCategory3 IS NOT NULL) and (@newCategory3 <> '')
			INSERT INTO CategoryRequest
			(BusinessId, CategoryName)
			VALUES (@BusinessId, @newCategory3)
						
		SELECT @IdResult AS IdResult

	COMMIT TRANSACTION
END
go
ALTER PROCEDURE [dbo].[spBusiness_GetList]
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		Business.BusinessId,
		Business.[Login],
		Business.Email,
		Business.[Password],
		Business.BusinessName, 
		Business.Web,
		Business.BusinessPhone,
		Business.CellPhone,
		Business.TollFreePhone,
		Business.Fax,
		Business.StreetAddress1,
		Business.StreetAddress2, 
		Business.IsActive,
		Business.Deleted, 
		Business.City,
		Business.StateCode,
		Business.ZipCode,
		Business.[Description], 
		Business.CreditCardNo,
		Business.CreditCardExpDate,
		Business.CreditCardName,
		Business.CreditCardPin, 
		Business.BillingAddress1,
		Business.BillingAddress2,
		Business.BillingCity,
		Business.BillingStateCode,
		Business.BillingZipCode,
		Business.BillingUse, 
		Business.Latitude,
		Business.Longitude, 
		CONVERT(VARBINARY(MAX), NULL) AS Picture,
		Business.PictureContentType, 
		Business.ReferencedBy,
		Business.DateAdded,
		Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
		vwLike.Likes,
		vwLike.Dislikes,
		vwBusinessRating.Rating,
		vwBusinessRating.CountReviews AS ReviewsCount
	FROM Business (NOLOCK) LEFT JOIN
		(SELECT BusinessId,
			SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
			SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes
		FROM BusinessLike (nolock)
		GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId	
		LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
	WHERE Deleted = 0
		and IsRegistered = 1
END
go
